package com.csii.adhoc.dataready.serviceImpl;

import com.csii.adhoc.JdbcTemplate.DbUilts;
import com.csii.adhoc.dataready.*;
import com.csii.adhoc.dataready.service.*;
import com.csii.adhoc.dataunits.TDataOrd;
import com.csii.adhoc.dataunits.addcolumn.DataNewFld;
import com.csii.adhoc.dataunits.service.DataUnitsService;
import com.csii.adhoc.dblink.DbLinkVo;
import com.csii.adhoc.dblink.service.DbLinkService;
import com.csii.adhoc.exception.ParamValidityException;
import com.csii.adhoc.id.UidGenerator;
import com.csii.common.DbLinkUtils;
import com.csii.common.constant.CommonConstant;
import com.csii.result.APIResult;
import com.csii.result.PageVo;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.annotation.Resource;
import java.util.*;

/**
 * @description:
 * @author:
 * @create:
 */
@Service
@Slf4j
public class DatasetQueryServiceImpl implements DatasetQueryService {
    @Autowired
    private TDataTaskService tDataTaskService;
    @Autowired
    DbLinkService dbLinkService;
    @Autowired
    TDataFldService tDataFldService;
    @Autowired
    TDataFromService tDataFromService;
    @Autowired
    TDataRelationService tDataRelationService;
    @Autowired
    TDataRelationDtlService tDataRelationDtlService;
    @Autowired
    TDataMergeService tDataMergeService;
    @Autowired
    TDataMergeDtlService tDataMergeDtlService;
    @Autowired
    TDataCondService tDataCondService;
    @Autowired
    TDataParamService tDataParamService;
    @Resource
    private TMgnDirService tMgnDirService;
    @Autowired
    DbLinkUtils dbLinkUtils;
    @Autowired
    UidGenerator uidGenerator;
    @Autowired
    DataUnitsService dataUnitsService;

    @Override
    public PageVo<Map<String, Object>> tableListQuery(DbLinkVo vo) {
        String sql;
        //0-oracle,1-db2,2-mysql
        switch (vo.getDbtype()) {
            case "0":
                sql = "select b.comments,a.TABLE_NAME from user_tables a left join user_tab_comments b\n" +
                        " on a.TABLE_NAME = b.table_name where 1 = 1";
                break;
            case "1":
                sql = "select * from user_tables a";
                break;
            case "2":
                sql = "select TABLE_NAME,TABLE_COMMENT COMMENTS from information_schema.TABLES a where a.TABLE_SCHEMA=(select database())";
                break;
            default:
                throw new ParamValidityException("未知的数据库类型，dbtype:" + vo.getDbtype());
        }
        if (!StringUtils.isEmpty(vo.getTableName())){
            String tableName = vo.getTableName();
            tableName = tableName.replace("_","\\_");
            sql = sql + " and a.TABLE_NAME like '%" + tableName + "%'";
            if ("0".equals(vo.getDbtype())){
                sql = sql + " escape '\\'";
            }
        }
        return dbLinkUtils.getPageSqlResult(null,vo.getConnId(), sql, vo.getPageNo(), vo.getPageSize());
    }


    @Override
    public List<Map<String, Object>> allFieldQuery(TDataTaskPo vo) {
        List<Map<String, Object>> list;
        //1数据集,2来源表,3 SQL
        switch (vo.getIdType()) {
            case "1":
                list = getDbsetAllFleld(vo);
                break;
            case "2":
                list = getOneTableAllFleld(vo);
                break;
            default:
                log.error("无效的数据集类型，只能是1数据集,2来源表,3 SQL，idType" + vo.getIdType());
                throw new ParamValidityException("无效的数据集类型，只能是1数据集,2来源表,3 SQL，idType" + vo.getIdType());
        }

        return list;

    }
    @Transactional
    @Override
    public TDataRelationPo assoViewMaintain(TDataRelationPo vo) {
        if (vo.getOptMode().equals("A") || vo.getOptMode().equals("U")) {
            if (vo.getLists() == null || vo.getLists().size() < 1) {
                log.error("关联明细条件不能为空！");
                throw new ParamValidityException("关联明细条件不能为空！");
            }
            for (TDataRelationDtlPo p : vo.getLists()) {
                if (!p.getMFldType().equals(p.getSFldType())) {
                    log.error("关联明细数据类型不符！");
                    throw new ParamValidityException("关联明细数据类型不符！");
                }
            }
        }
        if (CommonConstant.OPT_MODE_ADD.equals(vo.getOptMode())) {
            vo.setRelationId(uidGenerator.getUID() + "");
        }
        tDataRelationService.tDataRelationMaintain(vo, vo.getOptMode());
        List<TDataRelationDtlPo> lists = vo.getLists();
        if (lists != null) {
            lists.forEach(x -> x.setRelationId(vo.getRelationId()));
        }
        switch (vo.getOptMode()) {
            case "A":
                tDataRelationDtlService.insertListTDataRelationDtl(lists);
                break;
            case "D":
                tDataRelationDtlService.deleteByRelationId(vo.getRelationId());
                break;
            case "U":
                tDataRelationDtlService.deleteByRelationId(vo.getRelationId());
                tDataRelationDtlService.insertListTDataRelationDtl(vo.getLists());
                break;
            default:
                log.error("无效的操作标志:" + vo.getOptMode());
                throw new ParamValidityException("无效的操作标志:" + vo.getOptMode());
        }
        return vo;
    }

    @Override
    public List<TDataMergePo> tDataMergeDtlQuery(TDataMergePo vo) {
        List<TDataMergeDtlPo> tdrdps;
        TDataTaskPo tDataTaskPo;

        List<TDataMergePo> tdrps = tDataMergeService.selectListTDataMerge(vo.getModelId());
        if (tdrps == null) {
            log.error("该数据集id无左右合并记录，modelId:" + vo.getModelId());
            throw new ParamValidityException("该数据集id无左右合并记录，modelId:" + vo.getModelId());
        }

        //查询表显示名称
        tDataTaskPo = tDataTaskService.selectByPrimaryKey(vo.getModelId());
        if (tDataTaskPo == null) {
            log.error(_LINE_() + "该数据集id无任务记录，modelId:" + vo.getModelId());
            throw new ParamValidityException("该数据集id无任务记录，modelId:" + vo.getModelId());
        }
        for (TDataMergePo tdrp : tdrps) {
            tdrp.setMasterId(tDataTaskPo.getTableId());
            //名称按菜单表名称
            TMgnDirPo tMgnDirPo = tMgnDirService.selectTMgnDirByPrimaryKey(tDataTaskPo.getTableId());
            if (tMgnDirPo != null) {
                tdrp.setMasterTableName(tMgnDirPo.getName());
            }

            tMgnDirPo = tMgnDirService.selectTMgnDirByPrimaryKey(tdrp.getSlaveId());
            if (tMgnDirPo != null) {
                tdrp.setSlaveTableName(tMgnDirPo.getName());
            }

            tdrdps = tDataMergeDtlService.selectListTDataMergeDtl(tdrp.getRelationId());
            tdrp.setLists(tdrdps);
        }
        return tdrps;
    }

    @Override
    public List<TDataRelationPo> tDataRelationDtlQuery(TDataRelationPo vo) {
        List<TDataRelationDtlPo> tdrdps;
        TDataTaskPo tDataTaskPo;
        //数据集关联查询
        List<TDataRelationPo> tdrps = tDataRelationService.selectListTDataRelation(vo.getModelId());
        if (tdrps == null) {
            log.error(_LINE_() + "该数据集id无左右合并记录，modelId:" + vo.getModelId());
            throw new ParamValidityException("该数据集id无左右合并记录，modelId:" + vo.getModelId());
        }

        for (TDataRelationPo tdrp : tdrps) {
            //查询表显示名称 T_DATA_TASK
            tDataTaskPo = tDataTaskService.selectByPrimaryKey(tdrp.getModelId());
            if (tDataTaskPo == null) {
                log.error(_LINE_() + "该数据集id无任务信息，modelId:" + tdrp.getModelId());
                throw new ParamValidityException("该数据集id无任务信息，modelId:" + tdrp.getModelId());
            }
            //名称按菜单表名称 T_MGN_DIR
            TMgnDirPo tMgnDirPo = tMgnDirService.selectTMgnDirByPrimaryKey(tDataTaskPo.getTableId());
            if (tMgnDirPo != null) {
                tdrp.setMasterTableName(tMgnDirPo.getName());
            }

            tMgnDirPo = tMgnDirService.selectTMgnDirByPrimaryKey(tdrp.getSlaveId());
            if (tMgnDirPo != null) {
                tdrp.setSlaveTableName(tMgnDirPo.getName());
            }

            tdrdps = tDataRelationDtlService.selectListTDataRelationDtl(tdrp.getRelationId());
            tdrp.setLists(tdrdps);
        }
        return tdrps;
    }

    @Transactional
    @Override
    public TDataMergePo tDataMergeMaintain(TDataMergePo vo) {
        tDataMergeService.tDataMergeMaintain(vo, vo.getOptMode());
        if (vo.getOptMode().equals("A") || vo.getOptMode().equals("U")) {
            if (vo.getLists() == null || vo.getLists().size() < 1) {
                log.error(_LINE_() + "左右合并明细条件不能为空！");
                throw new ParamValidityException("左右合并明细条件不能为空！");
            }
            for (TDataMergeDtlPo p : vo.getLists()) {
                if (!p.getMFldType().equals(p.getSFldType())) {
                    log.error(_LINE_() + "左右合并明细数据类型不符！");
                    throw new ParamValidityException("左右合并明细数据类型不符！");
                }
            }
        }
        switch (vo.getOptMode()) {
            case "A":
                tDataMergeDtlService.insertListTDataMergeDtl(vo.getLists());
                break;
            case "D":
                tDataMergeDtlService.deleteByMergeId(vo.getRelationId());
                break;
            case "U":
                //先删除，再插入
                tDataMergeDtlService.deleteByMergeId(vo.getRelationId());
                tDataMergeDtlService.insertListTDataMergeDtl(vo.getLists());
                break;
            default:
                log.error(_LINE_() + "无效的操作标志:" + vo.getOptMode());
                throw new ParamValidityException("无效的操作标志:" + vo.getOptMode());
        }
        return vo;
    }

    @Transactional
    @Override
    public TDataTaskPo custTableMaintain(TDataTaskPo vo) {

        List<TDataFromPo> list = vo.getTdfrs();

        tDataTaskService.tDataTaskMaintain(vo, vo.getOptMode());

        switch (vo.getOptMode()) {
            case "A":
            case "U":
                for (TDataFromPo p : list) {
                    List<TDataFldPo> flds = p.getTdfds();
                    //"导入数据库表"将t_data_from的表显示名设成modelname一致
                    if ("3".equals(vo.getModelType())) {
                        p.setTableShowname(vo.getModelName());
                    }
                    //modeltype="4",自定义数据集新增时引用"导入数据库表“时引入的表
                    if ("A".equals(vo.getOptMode()) && !"4".equals(vo.getModelType())) {
                        tDataFromService.tDataFromMaintain(p, vo.getOptMode());
                    }
                    if (flds.size() > 0) {
                        tDataFldService.deleteByModelId(vo.getModelId());
                        for (TDataFldPo fld : flds) {
                            fld.setModelId(vo.getModelId());
                            fld.setTableName(p.getTableName());
                            tDataFldService.insertOneTDataFld(fld);
                        }
                    }
                }
                break;
            case "D":
                for (TDataFromPo p : list) {
                    List<TDataFldPo> flds = p.getTdfds();
                    tDataFromService.deleteTDataFromByPrimaryKey(p.getTableId());
                    if (flds.size() > 0) {
                        // for(TDataFldPo fld : flds ) { //删除一次即可、不需要循环删除
                        tDataFldService.deleteByModelId(vo.getModelId());
                        // }
                    }
                }
                break;
            default:
                log.error(_LINE_() + "操作标志出错，optMode:" + vo.getOptMode());
                throw new ParamValidityException("操作标志出错，optMode:" + vo.getOptMode());
        }
        return vo;
    }

    @Transactional
    @Override
    public TDataTaskPo custSqlMaintain(TDataTaskPo vo) {
        List<TDataParamPo> list = vo.getTdpps();
        TDataFromPo tDataFromPo = new TDataFromPo();
        String sql = vo.getPcWhere();
        List<TDataParamPo> ps = vo.getTdpps();
        tDataTaskService.tDataTaskMaintain(vo, vo.getOptMode());
        for (TDataParamPo p : ps) {
            //1-文本，2-日期，3-数值
            if ("1".equals(p.getParamType())) {//1放前面防止空指针
                sql = sql.replace(p.getParamName(), "'" + p.getDefaultVal() + "'");
            } else if ("2".equals(p.getParamType())) {
                sql = sql.replace(p.getParamName(), "to_date(" + p.getDefaultVal() + ",'YYYYMMDD')");
            } else {
                sql = sql.replace(p.getParamName(), p.getDefaultVal());
            }
        }
        vo.setPcWhere(sql);
        PageVo<Map<String, Object>> pageSqlResult = dbLinkUtils.getPageSqlResult(vo.getModelId(),vo.getConnId(), vo.getPcWhere(), 1, 1);
        switch (vo.getOptMode()) {
            case "A":
            case "U":
                for (TDataParamPo p : list) {
                    if (p.getParamType() == null ||
                            !("1".equals(p.getParamType())
                                    || "2".equals(p.getParamType())
                                    || "3".equals(p.getParamType()))) {
                        log.error(_LINE_() + "未知的参数类型，paramtype:" + p.getParamType());
                        throw new ParamValidityException("未知的参数类型，paramtype:" + p.getParamType());
                    }
                    tDataParamService.tDataParamMaintain(p, vo.getOptMode());
                }
                if (pageSqlResult.getRecords().size() > 0) {
                    tDataFldService.deleteByModelId(vo.getModelId());
                    for (Map.Entry<String, Object> entry : pageSqlResult.getRecords().get(0).entrySet()) {
                        TDataFldPo tDataFldPo = new TDataFldPo();
                        tDataFldPo.setModelId(vo.getModelId());
                        tDataFldPo.setTableName(vo.getPcWhere());
                        tDataFldPo.setFldCnname(entry.getKey());
                        tDataFldPo.setFldEnname(entry.getKey());
                        tDataFldPo.setFldShowname(entry.getKey());
                        tDataFldPo.setFldType("VARCHAR");
                        tDataFldPo.setFldLen("2000");
                        tDataFldPo.setTableName("2000");
                        tDataFldService.insertOneTDataFld(tDataFldPo);
                    }

                }
                tDataFromPo.setTableId(vo.getTableId());
                tDataFromPo.setConnId(vo.getConnId());
                tDataFromPo.setSrcTable(vo.getPcWhere());
                tDataFromPo.setTableName("1111");
                tDataFromPo.setSrcType("1");
                tDataFromService.tDataFromMaintain(tDataFromPo, vo.getOptMode());
                break;
            case "D":
                tDataParamService.deleteByModelId(vo.getModelId());
                break;
            default:
                log.error(_LINE_() + "操作标志出错，optMode:" + vo.getOptMode());
                throw new ParamValidityException("操作标志出错，optMode:" + vo.getOptMode());
        }
        return vo;
    }

    @Override
    public PageVo<Map<String, Object>> tDataTempQuery(TDataViewPo po) {
        String connId = po.getConnId();
        StringBuilder srcTab = new StringBuilder();
        StringBuilder selList = new StringBuilder();
        String sql;
        List<TDataFld> tDataFlds;
        PageVo<Map<String, Object>> pageSqlResult = new PageVo<>();
        sql = "select ";
        char tmp = 'a';
        int pageNo = Integer.parseInt(po.getPageNo());
        int pageSize = Integer.parseInt(po.getPageSize());
        //sql查询，直接返回结果
        if (po.getSql() != null && !po.getSql().trim().isEmpty()) {
            sql = po.getSql();
            List<TDataParamPo> ps = po.getTdpps();
            for (TDataParamPo p : ps) {
                //1-文本，2-日期，3-数值
                if ("1".equals(p.getParamType())) {//1放前面防止空指针
                    sql = sql.replace(p.getParamName(), "'" + p.getDefaultVal() + "'");
                } else if ("2".equals(p.getParamType())) {
                    sql = sql.replace(p.getParamName(), "to_date(" + p.getDefaultVal() + ",'YYYYMMDD')");
                } else {
                    sql = sql.replace(p.getParamName(), p.getDefaultVal());
                }
            }
            //Page<Map<String, Object>> page = dbLinkUtils.getPageSqlResult(connId, sql, pageNo, pageSize);
            pageSqlResult = dbLinkUtils.getPageSqlResult(po.getModelId(),connId, sql, pageNo, pageSize);
            if (pageSqlResult.getRecords().size() < 1) {
                log.error(_LINE_() + "查询结果为空");
            }
            return pageSqlResult;
        }

        if (po.getTableLists()==null || po.getTableLists().get(0)==null){
            return pageSqlResult;
        } else if (po.getTableLists().get(0).getFldLists()==null || po.getTableLists().get(0).getFldLists().size()==0){
            return pageSqlResult;
        }
        List<TDataTable> tableLists = po.getTableLists();
        if (tableLists == null || tableLists.size() < 1) {
            log.error(_LINE_() + "上传表名为空!");
            throw new ParamValidityException("上传表名为空!");
        }
        if (tableLists.size() > 1) {
            tableLists.sort(Comparator.comparingInt(TDataTable::getSeqNo));//这方法需要jdk1.8以上
        }

        if (tableLists.size() > 26 || tableLists.size() <= 0) {
            log.error(_LINE_() + "关联表数量异常，size:" + tableLists.size());
            throw new ParamValidityException("关联表数量异常，size:" + tableLists.size());
        }

        Map<String, Integer> m1 = new HashMap<>();
        for (TDataTable t : tableLists) {
            srcTab.append(t.getTableName()).append(" ").append(tmp);
//            srcTab += t.getTableName() ;
            tDataFlds = t.getFldLists();
            if (tDataFlds.size() > 1) {
                tDataFlds.sort(Comparator.comparingInt(TDataFld::getSeqNo));//这方法需要jdk1.8以上
            }

            //查询重复的字段
            List<TDataFld> repeatList = new ArrayList<>();
            for (TDataFld f : tDataFlds) {
                if (m1.containsKey(f.getFldName())) {
                    repeatList.add(f);

                    Integer num = m1.get(f.getFldName());
                    m1.put(f.getFldName(), num + 1);
                } else {
                    m1.put(f.getFldName(), 1);
                }
            }

            int i = 1;
            for (TDataFld f : tDataFlds) {
                //flag第1位：新增列标志:0普通，1新增；
                if (f.getFlag() != null && f.getFlag().substring(0, 1).equals("1")) {

                } else {
                    Integer num = m1.get(f.getFldName());
                    if (num == 1) {
                        selList.append(tmp).append(".").append(f.getFldName());
                    } else {
                        selList.append(tmp).append(".").append(f.getFldName()).append(" ").append(f.getFldName()).append("#").append(i);
                        i++;
                    }
                }
//                selList += f.getFldName();
                selList.append(",");
            }
            srcTab.append(",");
            tmp += 1;
        }
        String selListStr = selList.substring(0, selList.length() - 1);
        srcTab = new StringBuilder(srcTab.substring(0, srcTab.length() - 1));

        sql += selListStr + " from " + srcTab;

        pageSqlResult = dbLinkUtils.getPageSqlResult(po.getModelId(),connId, sql, pageNo, pageSize);
        return pageSqlResult;
    }

    @Override
    public TDataTaskPo tDataTaskQuery(TDataTaskPo po) {
        Boolean humpNameFlag = po.getHumpNameFlag();//是否驼峰返回
        PageVo<Map<String, Object>> page = null;
        int pageNo;
        int pageSize;
        TDataTaskPo tDataTaskPo;
        String sql;

        String modelId = po.getModelId();
        tDataTaskPo = tDataTaskService.selectByPrimaryKey(modelId);
        if (tDataTaskPo == null) {
            log.error(_LINE_() + _LINE_() + "该数据集id无任务信息，modelId：" + modelId);
            throw new ParamValidityException("该数据集id无任务信息，modelId：" + modelId);
        }
        if (po.getOptMode() != null && "Q".equals(po.getOptMode())) {
            return po;
        }

        pageNo = Integer.parseInt(po.getPageNo());
        pageSize = Integer.parseInt(po.getPageSize());

        String connId = tDataTaskPo.getConnId();
        //dbLinkPo = dbLinkService.selectDbLinkByPrimaryKey(connId);//查询的dbLinkPo未使用、暂时注释
        String modelType = tDataTaskPo.getModelType();
        if (modelType.length() > 1) {
            modelType = modelType.substring(1, 2);
        }
        //model_type 1-sql,2-EXCEL,3-数据库，4-自助数据集
        switch (modelType) {
            case "1":
                //后台保存的sql，取出来直接执行，返回结果到前端
                List<TDataParamPo> ps = tDataParamService.selectListTDataParam(po.getModelId());
                sql = tDataTaskPo.getPcWhere();
                for (TDataParamPo p : ps) {
                    //1-文本，2-日期，3-数值
                    if ("1".equals(p.getParamType())) {//1放前面防止空指针
                        sql = sql.replace(p.getParamName(), "'" + p.getDefaultVal() + "'");
                    } else if ("2".equals(p.getParamType())) {
                        sql = sql.replace(p.getParamName(), "to_date(" + p.getDefaultVal() + ",'YYYYMMDD')");
                    } else {
                        sql = sql.replace(p.getParamName(), p.getDefaultVal());
                    }
                }
                page = dbLinkUtils.getPageSqlResult(po.getModelId(),connId, sql, pageNo, pageSize);
                break;
            case "2":
                break;
            case "3":
                //自定义sql查询，组装查询数据库表的sql，例："select * from nc_subj_bal where data_date='20201231'" ,
                // select列表还未从t_data_fld取。
                List<TDataFldPo> tdf = tDataFldService.selectListTDataFld(modelId);
                StringBuilder bf = new StringBuilder();
                String str;
                for (TDataFldPo p : tdf) {
                    bf.append("\"").append(p.getTableName()).append("\"").append(".").append(p.getFldEnname()).append(",");
                }
                str = bf.toString();
                str = str.substring(0, str.length() - 1);
                sql = "select 'select " + str + " from \"'||b.table_name||'\" '||b.alias||case when a.pc_where!=null then ' where ' else '' end||a.pc_where||' '||\n" +
                        "       case when a.pc_grp!=null then ' group by ' else '' end||a.pc_grp str_sql\n" +
                        "       from t_data_task a left join t_data_from b\n" +
                        "       on a.table_id = b.table_id where a.model_id ='" + modelId + "'";
                Map m1 = tDataTaskService.selCustMapDataDbTable(sql);
//                Map m1 = dbLinkUtils.getSqlResult(po.getConnId(),sql);
                if (m1 == null) {
                    log.error(_LINE_() + "该数据集id无任务记录，modelId:" + modelId);
                    throw new ParamValidityException("该数据集id无任务记录，modelId:" + modelId);
                }
                sql = (String) m1.get("STR_SQL");
                page = dbLinkUtils.getPageSqlResult(po.getModelId(),connId, sql, pageNo, pageSize);
                break;
            case "4":
                //自定义数据集，要从n张表里拼装出sql
                String fldList = getFldList(tDataTaskPo);
                String[] newFldList = getNewFldList(tDataTaskPo);
                String fromList = getFromList(tDataTaskPo);
                String condList = null;
                String gpList = tDataTaskPo.getPcGrp();
                String orderList = tDataTaskPo.getPcOrder();
                //TODO 新增列返显待测试
                String groupBySql = newFldList[1];
//                if (StringUtils.isEmpty(groupBySql)){
//                    sql = "select " + fldList + "," + newFldList[0] + " from " + fromList;
//                }else {
//                    sql = "select " + fldList + "," + newFldList[0] + " from " + fromList + "group by "+newFldList[1];
//                }
                sql = "select " + fldList + " from " + fromList;
                if (condList != null && !condList.trim().isEmpty()) {
                    sql += " and " + condList;
                }
                if (gpList != null && !gpList.trim().isEmpty()) {
                    sql += " group by " + gpList;
                }
                if (orderList != null && !orderList.trim().isEmpty()) {
                    sql += " order by " + orderList;
                }

                page = dbLinkUtils.getPageSqlResult(po.getModelId(),connId, sql, pageNo, pageSize);
                break;
            default:
                log.error(_LINE_() + "ModeType值不合法:" + modelType);
                throw new ParamValidityException("ModeType值不合法,只能为1、2、3、4；modelType:" + modelType);
        }
        po = tDataTaskPo;
        if (page != null) {
            po.setPage(page);
            if (Optional.ofNullable(humpNameFlag).orElse(false)) {//组件需要放回驼峰将数据转驼峰返回
                List<Map<String, Object>> maps = DbUilts.setHumpNameKey(page.getRecords());
                po.getPage().setRecords(maps);
            }
        }
        return po;
    }

    /**
     * create by: zyliu
     * description: 来源为数据集，取数据集的全字段列表
     * create time: 2021/1/18 23:06
     *
     */
    private List<Map<String, Object>> getDbsetAllFleld(TDataTaskPo vo) {
        TDataTaskPo tDataTaskPo = tDataTaskService.selectByPrimaryKey(vo.getModelId());
        if (tDataTaskPo == null) {
            log.error(_LINE_()+"该数据集id无任务记录，modelId:" + vo.getModelId());
            throw new ParamValidityException("该数据集id无任务记录，modelId:" + vo.getModelId());
        }
        //TDataFromPo  tDataFromPo = tDataFromService.selectOneTDataFrom(tDataTaskPo.getTableId());

        String dbtype = dbLinkService.selectDbLinkByPrimaryKey(tDataTaskPo.getConnId()).getDbtype();
        if (dbtype == null) {
            log.error("该连接id无记录，connId" + vo.getConnId());
            throw new ParamValidityException("该连接id无记录，connId" + vo.getConnId());
        }
        //0-oracle,1-db2,2-mysql
        String sql = "";
        switch (dbtype) {
            case "0":
                sql = "select table_id tableId,table_name tableName,fld_showname comments,fld_enname column_name,fld_type data_type," +
                        " case when instr(fld_len,',')>0 then substr(fld_len,0,instr(fld_len,',')-1) else fld_len end data_length," +
                        " case when instr(fld_len,',')>0 then substr(fld_len,instr(fld_len,',')+1,length(fld_len)-instr(fld_len,',')+1) else '' end data_precisioin,seqno colunm_id " +
                        " from t_data_fld where upper(model_id)=upper('" + vo.getModelId() + "')" +
                        " order by seqno";
                break;
            case "1":
                break;
            case "2":
                sql = "select table_id tableId,table_name tableName,fld_showname comments,fld_enname column_name,fld_type data_type" +
                        " from t_data_fld where model_id='" + vo.getModelId() + "'" +
                        " order by seqno";
                break;
            default:
                log.error(_LINE_()+"不支持的数据库类型，dbtype" + dbtype);
                throw new ParamValidityException("不支持的数据库类型，dbtype" + dbtype);
        }
        List<Map<String, Object>> l1 = tDataTaskService.selCustListDataDbTable(sql);
        if (l1 == null) {
            log.error(_LINE_()+"该数据集id没有选择字段，modelId:" + vo.getModelId());
        }
        List<Map<String, Object>> change = change(l1);
        return change;
    }
    /**
     * create by: zyliu
     * description: 来源为数据库表,t_data_task,t_data_relation,t_data_from
     * create time: 2021/1/18 23:06
     *
     */
    private List<Map<String, Object>> getOneTableAllFleld(TDataTaskPo vo) {
        TDataTaskPo tDataTaskPo = tDataTaskService.selectByPrimaryKey(vo.getModelId());
        String dbtype = dbLinkService.selectDbLinkByPrimaryKey(vo.getConnId()).getDbtype();
        if (dbtype == null) {
            log.error(_LINE_()+"该连接集id无记录，connId:" + vo.getConnId());
            throw new ParamValidityException("该连接集id无记录，connId:" + vo.getConnId());
        }


        //取主表名
        String mTable = "";
        if (tDataTaskPo != null) {
            TDataFromPo tDataFromPo = tDataFromService.selectOneTDataFrom(tDataTaskPo.getTableId());
            mTable = tDataFromPo.getTableName().trim();
        }else {
            mTable=vo.getModelId();
        }

        //0-oracle,1-db2,2-mysql
        String sql;
        switch (dbtype) {
            case "0":
                if (tDataTaskPo != null) {
                    sql = "select '" + tDataTaskPo.getTableId() + "' tableId,'" + mTable + "' tableName,t3.COLUMN_NAME,t3.DATA_TYPE,t4.NICK_NAME AS comments,t4.COL_TYPE ,t3.DATA_LENGTH,t3.DATA_PRECISION,t3.column_id" +
                            " from user_tab_columns t3  left join T_DATA_NICKNAME_INFO t4 on t4.COL_NAME = t3.COLUMN_NAME  and t4.MODEL_ID = '"+ vo.getModelId()+"'  where t3.TABLE_NAME=upper('" + mTable + "')" +
                            " order by column_id";
                } else {//如果根据tableid查询不到就当成表名直接去查库,上传的只有表名的情况，一般为首次选数据库表
                    sql = "select '" + vo.getModelId() + "' tableName,t4.comments,t3.COLUMN_NAME,t3.DATA_TYPE,t3.DATA_LENGTH,t3.DATA_PRECISION,t3.column_id" +
                            " from user_tab_columns t3,user_col_comments t4 where t3.TABLE_NAME='" + vo.getModelId() + "'" +
                            " and t3.TABLE_NAME = t4.table_name" +
                            " and t3.COLUMN_NAME=t4.column_name order by column_id";
                }
                break;
            case "2":
                sql = "select TABLE_NAME,COLUMN_COMMENT COMMENTS,COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH DATA_LENGTH," +
                        "ORDINAL_POSITION COLUMN_ID from information_schema.COLUMNS where TABLE_SCHEMA = " +
                        "(select database()) and TABLE_NAME='" + mTable + "'";
                break;
            default:
                log.error(_LINE_()+"不支持的数据库类型，dbtype:" + dbtype);
                throw new ParamValidityException("不支持的数据库类型，dbtype:" + dbtype);
        }
        PageVo<Map<String, Object>> pageSqlResult =
                dbLinkUtils.getPageSqlResult(vo.getModelId(),vo.getConnId(), sql, Integer.parseInt(vo.getPageNo()), Integer.parseInt(vo.getPageSize()));
        if (pageSqlResult.getRecords().size() == 0) {
            log.error(_LINE_()+"没查询到对应记录，modelId:" + vo.getModelId());
        }
        return pageSqlResult.getRecords();
    }

    // 当前行号
    public static int _LINE_() {
        StackTraceElement traceElement = ((new Exception()).getStackTrace())[1];
        return traceElement.getLineNumber();
    }

    private String[] getNewFldList(TDataTaskPo po) {
        String[] ret = new String[2];
        StringBuilder selectFrom = new StringBuilder();
        StringBuilder groupBy = new StringBuilder();
        String rule;
        List<DataNewFld> tfps = tDataFldService.selectListTDataNewFld(po.getModelId());
        for (int i = 0; i < tfps.size(); i++) {
            DataNewFld c = tfps.get(i);
            String type = c.getType() == null ? "" : c.getType();
            switch (type) {
                //公式函数
                case "1":
                    if (c.getValue1() != null && !"".equals(c.getValue1()) && c.getColName() != null && !"".equals(c.getColName())) {
                        selectFrom.append(c.getValue1())
                                .append("as ")
                                .append(c.getColName());
                    } else {
                        continue;
                    }
                    break;
                //时间差
                case "2":
                    // YEAR -sql :select trunc(months_between(sysdate,sysdate-(365*4)/12)) from dual;
                    // MOTH-sql: SELECT months_between(sysdate,TO_DATE('20210320', 'yyyyMMdd')) FROM dual;
                    // DAY -sql: select floor(sysdate - to_date('20210401','yyyymmdd')) AS "DAY"  from dual;
                    if (c.getValue1() != null && !"".equals(c.getValue1())
                            && c.getValue2() != null && !"".equals(c.getValue2())
                            && c.getValue3() != null && !"".equals(c.getValue3())
                            && c.getColName() != null && !"".equals(c.getColName())) {
                        // value3 存储的是 年 季度 月 天 时 分 秒
                        switch (c.getValue3()) {
                            //年
                            case "1":
                                selectFrom.append("trunc(months_between(sysdate,TO_DATE(")
                                        // .append()
                                        .append(")))");
                                break;

                            //季度
                            case "2":
                                break;

                            // 月
                            case "3":
                                break;

                            // 天
                            case "4":
                                break;

                            //时
                            case "5":
                                break;

                            //分
                            case "6":
                                break;

                            //秒
                            case "7":
                                break;
                        }
                        selectFrom.append(c.getValue1())
                                .append("as ")
                                .append(c.getColName());
                    } else {
                        continue;
                    }
                    break;
                //获取时间
                case "3":
                    //所有值-累计值 、 所有值-组内所有值
                case "4":
                    // rule 为 1 时，表示所有，rule 为 2 时，表示组内所有值
                    rule = c.getValue1() == null ? "" : c.getValue1();
                    if (c.getValue2() != null && !"".equals(c.getValue2())
                            && c.getValue3() != null && !"".equals(c.getValue3())
                            && c.getColName() != null && !"".equals(c.getColName())) {
                        switch (c.getValue3()) {
                            case "1": // 求和
                                selectFrom.append("sum(");
                                break;
                            case "2": // 平均
                                selectFrom.append("avg(");
                                break;
                            case "3": // 最大值
                                selectFrom.append("max(");
                                break;
                            case "4": // 最小值
                                selectFrom.append("min(");
                                break;
                        }
                        selectFrom.append(c.getValue2())
                                .append(") as ")
                                .append(c.getColName());
                    } else {
                        continue;
                    }
                    //组选择和排序选择
                    if ("2".equals(rule)) {
                        List<TDataOrd> selects = dataUnitsService.getOrderByType(c.getModelId(), c.getColName(), "1");
                        for (TDataOrd select : selects) {
                            groupBy.append(select.getName());
                        }
                    }
                    break;
                //累计值 所有  、 累计值 组内累计
                case "5":
                    // rule 为 1 时，表示所有，rule 为 2 时，表示组内所有值
                    rule = c.getValue1() == null ? "" : c.getValue1();
                    if (c.getValue2() != null && !"".equals(c.getValue2())
                            && c.getValue3() != null && !"".equals(c.getValue3())
                            && c.getColName() != null && !"".equals(c.getColName())) {

                    }
                    break;
                case "6":
                    break;
                case "7":
                    break;
                case "8":
                    break;
                case "9":
                    break;
                default:
            }
            if (i < tfps.size() - 1) {
                selectFrom.append(",");
            }
        }
        ret[0] = selectFrom.toString();
        ret[1] = groupBy.toString();
        return ret;
    }
    /**
     * create by: zyliu
     * description: 组装select列表,t_data_fld
     * create time: 2021/1/18 23:05
     *
     * @return
     * @Param: null
     */
    String getFldList(TDataTaskPo po) {
        StringBuilder buf = new StringBuilder();
        String sql;

        List<TDataFldPo> tfps = tDataFldService.selectListTDataFld(po.getModelId());
        if (tfps == null || tfps.size() <= 0) {
            log.error(_LINE_() + "该数据集id无已选择字段，modelId:" + po.getModelId());
            throw new ParamValidityException("该数据集id无已选择字段，modelId:" + po.getModelId());
        }

        //查询重复的字段   key 字段、value 重复次数
        Map<String, Integer> m1 = new HashMap<>();
        for (TDataFldPo f : tfps) {
            if (m1.containsKey(f.getFldEnname())) {

                Integer num = m1.get(f.getFldEnname());
                m1.put(f.getFldEnname(), num + 1);
            } else {
                m1.put(f.getFldEnname(), 1);
            }
        }

        int i = 1;
        for (TDataFldPo tfp : tfps) {
            String fldName = tfp.getFldEnname().replaceAll(" ", "");
            Integer num = m1.get(tfp.getFldEnname());
            if (num > 1) {
                buf.append(tfp.getTableName()).append(".").append(fldName).append(" ").append(fldName).append("#").append(i).append(",");
                i++;
            } else {
                buf.append(tfp.getTableName()).append(".").append(fldName).append(",");
            }
        }

        sql = buf.toString();
        sql = sql.substring(0, sql.length() - 1);

        return sql;
    }
    /**
     * create by: zyliu
     * description: 组装来源表串,t_data_task,t_data_merge,t_data_from
     * create time: 2021/1/18 23:06
     *
     * @return
     * @Param: null
     */
    String getFromList(TDataTaskPo po) {
        StringBuilder buf = new StringBuilder();
        String sql;
        String mTable;
        TDataFromPo tDataFromPo;
        List<String> ids = new ArrayList<>();
        List<TDataFromPo> tdfs;
        List<TDataMergePo> tdrs;

        //取主表名
        tDataFromPo = tDataFromService.selectOneTDataFrom(po.getTableId());
        if (tDataFromPo == null) {
            log.error(_LINE_() + "该数据集id无来源表记录，tableId:" + po.getTableId());
            throw new ParamValidityException("该数据集id无来源表记录，tableId:" + po.getTableId());
        }
        mTable = tDataFromPo.getTableName() + ",";
        if (tDataFromPo.getAlias() != null) {
            mTable += " " + tDataFromPo.getAlias() + ",";
        }
        //取从表名,先取Id,再通过id到表来源里取名
        tdrs = tDataMergeService.selectListTDataMerge(po.getModelId());
        //主有主表,直接返回
        if (tdrs != null && tdrs.size() < 1) {
            sql = mTable;
            sql = sql.substring(0, sql.length() - 1);
            return sql;
        }
        if (tdrs != null) {
            for (TDataMergePo tdf : tdrs) {
                ids.add(tdf.getSlaveId());
                tdf.setMasterTableName(tDataFromPo.getTableName());
            }
        }
        tdfs = tDataFromService.selectListTDataFrom(ids);
        if (tdfs != null && tdfs.size() > 0) {
            for (TDataFromPo tdf : tdfs) {
                String sName = tdf.getTableName();
                if (tdf.getAlias() != null) {
                    sName += " " + tdf.getAlias();
                }
                buf.append(sName);
                buf.append(",");
            }
        }
        sql = mTable + buf.toString();
        sql = sql.substring(0, sql.length() - 1);

        //关联on条件，在条件后面加(+)来表示左右连接
        String onSql = getOnList(tdrs);

        sql = sql + onSql;

        return sql;
    }
    /**
     * create by: zyliu
     * description: 组装on关联条件,t_data_merge,t_data_merge_dtl
     * create time: 2021/1/18 23:08
     *
     * @return
     * @Param: null
     */
    String getOnList(List<TDataMergePo> tdrs) {
        StringBuilder sql = new StringBuilder();
        List<TDataMergeDtlPo> tdrds;
        StringBuilder ons = new StringBuilder(" where 1=1 and ");

        for (TDataMergePo tdf : tdrs) {
            tdrds = tDataMergeDtlService.selectListTDataMergeDtl(tdf.getRelationId());
            if (tdrds == null) {
                log.error(_LINE_() + "该关联id无合并明细记录，id:" + tdf.getRelationId());
                throw new ParamValidityException("该关联id无合并明细记录，id:" + tdf.getRelationId());
            }
            for (TDataMergeDtlPo tdrd : tdrds) {
                //RelationMode关联方式	char(1)	1-等值连接，2-左连接，3-右连接，4-全连接
                if (tdf.getRelationMode() == null) {
                    log.error(_LINE_() + "关联方式异常！relationModel:" + tdf.getRelationMode());
                    throw new ParamValidityException("关联方式异常！relationModel:" + tdf.getRelationMode());
                }
                if (tdf.getRelationMode().equals("2")) {
                    ons.append(tdf.getMasterTableName()).append(".").append(tdrd.getMasterFld()).append("=").append(tdf.getSlaveTableName()).append(".").append(tdrd.getSlaveFld()).append("(+)");
                } else if (tdf.getRelationMode().equals("3")) {
                    ons.append(tdf.getMasterTableName()).append(".").append(tdrd.getMasterFld()).append("(+)").append("=").append(tdf.getSlaveTableName()).append(".").append(tdrd.getSlaveFld());
                } else {
                    ons.append(tdf.getMasterTableName()).append(".").append(tdrd.getMasterFld()).append("=").append(tdf.getSlaveTableName()).append(".").append(tdrd.getSlaveFld());
                }
                ons.append(" and ");
            }
            sql.append("  ").append(ons);
            ons = new StringBuilder();
        }
        sql = new StringBuilder(sql.substring(0, sql.lastIndexOf(" and")));

        return sql.toString();
    }

    /**
     * create by: zyliu
     * description: 组装where条件,t_data_cond
     * create time: 2021/1/18 23:09
     *
     * @return
     * @Param: null
     */
    String getCondList(TDataTaskPo po) {
        StringBuilder bd = new StringBuilder();
        String t = "";
        String str = "";

        List<TDataCondPo> tdcs = tDataCondService.selectListTDataCond(po.getModelId());
        for (TDataCondPo tdc : tdcs) {
            if (tdc.getParentId().equals("0")) {
                if (!(tdc.getCondOper().equals("and") || tdc.getCondOper().equals("or"))) {
                    bd.append(tdc.getFldEnname()).append(tdc.getCondOper()).append("'").append(tdc.getValue()).append("'");
                    return bd.toString();
                }
                if (tdc.getCondOper().equals("and") || tdc.getCondOper().equals("or")) {
                    bd.append("((@@) ").append(tdc.getCondOper()).append(" (@@))");
                    str = bd.toString();
                }
            } else if (tdc.getCondOper().equals("and") || tdc.getCondOper().equals("or")) {
                t = "((@@) " + tdc.getCondOper() + " (@@))";
            } else {
                t = "" + tdc.getFldEnname() + " " + tdc.getCondOper() + " " + "'" + tdc.getValue() + "'";
            }
            if (t != null) {
                System.out.println("== :" + str);
                str = str.replaceFirst("@@", t);
                t = null;
            }

        }
        System.out.println("json00 :" + bd.toString());
        System.out.println("json str00 :" + str);

        return str;
    }

    public List<Map<String,Object>> change(List<Map<String,Object>> list){
        for (Map<String,Object> m: list) {
           if ("未知".equals(m.get("COMMENTS"))){
               m.put("COMMENTS",m.get("COLUMN_NAME"));
           }
        }
        return list;
    }
}
